﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Globalization;
using System.Text;

namespace OrderService.Utils
{
    public delegate void ReceivedDataEventHandler(SqlDataReader reader);

    public sealed class DBUtil
    {
        private static int TimeOut = 1200;

        public static bool ExecuteStoreProcedure(string sql, string ConnectString, params SqlParameter[] sqlParams)
        {
            if (!string.IsNullOrEmpty(sql))
            {
                try
                {
                    using (SqlConnection conn = new SqlConnection(ConnectString))
                    {
                        conn.Open();
                        using (SqlCommand cmd = new SqlCommand(sql))
                        {
                            cmd.Connection = conn;
                            cmd.CommandTimeout = TimeOut;
                            cmd.CommandType = CommandType.StoredProcedure;

                            if (sqlParams != null && sqlParams.Length > 0)
                            {
                                cmd.Parameters.Clear();
                                cmd.Parameters.AddRange(sqlParams);
                            }

                            cmd.ExecuteScalar();

                            cmd.Parameters.Clear();

                            return true;
                        }
                    }
                }
                catch (SqlException ex)
                {
                    throw new Exception(string.Format("Error while executing SQL: \r\n{0} \r\nParams:{1} at \r\n{2}", sql, SQLParamsToString(sqlParams), ConnectString), ex);
                }
            }

            return false;
        }

        private static string SQLParamsToString(params SqlParameter[] sqlParams)
        {
            StringBuilder sb = new StringBuilder();
            if (sqlParams != null)
            {
                foreach (SqlParameter item in sqlParams)
                {
                    sb.AppendLine(
                        string.Format(
                        CultureInfo.InvariantCulture,
                        "Name:{0}, Value:{1}, Type:{2}", item.ParameterName, item.SqlValue, item.SqlDbType)
                        );
                }
            }

            return sb.ToString();
        }

        public static bool RunSQLCmd(string sql, string ConnectString, params SqlParameter[] sqlParams)
        {
            if (!string.IsNullOrEmpty(sql))
            {
                try
                {
                    using (SqlConnection conn = new SqlConnection(ConnectString))
                    {
                        conn.Open();
                        using (SqlCommand cmd = new SqlCommand(sql))
                        {
                            cmd.Connection = conn;
                            cmd.CommandTimeout = TimeOut;
                            
                            if (sqlParams != null && sqlParams.Length > 0)
                            {
                                cmd.Parameters.Clear();
                                cmd.Parameters.AddRange(sqlParams);
                            }

                            cmd.ExecuteNonQuery();

                            cmd.Parameters.Clear();

                            return true;
                        }
                    }
                }
                catch (SqlException ex)
                {
                    throw new Exception(string.Format("Error while executing SQL: \r\n{0} \r\nParams:{1} at \r\n{2}", sql, SQLParamsToString(sqlParams), ConnectString), ex);
                }
            }

            return false;
        }

        public static DataTable FillDataTable(string sql, string ConnectString, params SqlParameter[] sqlParams)
        {
            DataTable dt = new DataTable();
            try
            {
                using (SqlConnection conn = new SqlConnection(ConnectString))
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.CommandTimeout = TimeOut;

                    if (sqlParams != null && sqlParams.Length > 0)
                    {
                        //cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddRange(sqlParams);
                    }

                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {   
                        da.Fill(dt);
                        cmd.Parameters.Clear();
                        return dt;
                    }
                }
            }
            catch (SqlException ex)
            {
                throw new Exception(string.Format("Error while executing SQL: \r\n{0} \r\nParams:{1} at \r\n{2}", sql, SQLParamsToString(sqlParams), ConnectString), ex);
            }
        }

        public static DataSet FillDataSet(string sql, string ConnectString, params SqlParameter[] sqlParams)
        {
            DataSet ds = new DataSet();
            try
            {
                using (SqlConnection conn = new SqlConnection(ConnectString))
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.CommandTimeout = TimeOut;

                    if (sqlParams != null && sqlParams.Length > 0)
                    {
                       // cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddRange(sqlParams);
                    }

                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        da.Fill(ds);
                        cmd.Parameters.Clear();
                        return ds;
                    }
                }
            }
            catch (SqlException ex)
            {
                throw new Exception(string.Format("Error while executing SQL: \r\n{0} \r\nParams:{1} at \r\n{2}", sql, SQLParamsToString(sqlParams), ConnectString), ex);
            }
        }

        public static bool ExecuteReader(string sql, string ConnectString, ReceivedDataEventHandler readDataEvent, params SqlParameter[] sqlParams)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(ConnectString))
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(sql))
                    {
                        cmd.Connection = conn;
                        cmd.CommandTimeout = TimeOut;

                        if (sqlParams != null)
                        {
                            cmd.Parameters.Clear();
                            cmd.Parameters.AddRange(sqlParams);
                        }

                        Stopwatch sw = new Stopwatch();
                        sw.Start();

                        using (SqlDataReader dataReader = cmd.ExecuteReader())
                        {
                            cmd.Parameters.Clear();
                            if (readDataEvent != null)
                            {
                                try
                                {
                                    readDataEvent(dataReader);
                                }
                                catch (Exception)
                                {
                                    throw;
                                }
                            }
                            dataReader.Close();
                        }

                        sw.Stop();

                        Trace.TraceInformation(
                             string.Format("SQL Command Executed: {0}\r\nDuration: {1}", sql, sw.Elapsed)
                             );
                            
                    }
                }
            }
            catch (SqlException ex)
            {
                throw new Exception(string.Format("Error while executing SQL: \r\n{0} \r\nParams:{1} at \r\n{2}", sql, SQLParamsToString(sqlParams), ConnectString), ex);
            }

            return true;
        }

        public static string RemovePipeLine(string s)
        {
            if (!string.IsNullOrEmpty(s))
            {
                return s.Replace("|", string.Empty);
            }
            return s;
        }

        public static string ForamtDateTime(object o)
        {
            if (o != DBNull.Value && o is DateTime)
            {
                DateTime dt = (DateTime)o;
                return dt.ToString("yyyyMMdd");
            }
            return string.Empty;
        }

        public static string ForamtString(object o)
        {
            return o == DBNull.Value ? string.Empty : o.ToString().Trim();
        }

        /// <summary>
        /// If a column name contains any of these special characters 
        /// ~ ( ) # \ / = > < + - * % & | ^ ' " [ ], 
        /// you must enclose the column name within square brackets [ ]. 
        /// If a column name contains right bracket ] or backslash \, 
        /// escape it with backslash (\] or \\).
        /// </summary>
        /// <param name="valueWithoutWildcards"></param>
        /// <returns></returns>
        public static string EscapeLikeValue(string valueWithoutWildcards)
        {
            valueWithoutWildcards = valueWithoutWildcards.Trim();

            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < valueWithoutWildcards.Length; i++)
            {
                char c = valueWithoutWildcards[i];
                switch (c)
                {
                    case '~':
                    case '(':
                    case ')':
                    case '#':
                    case '/':
                    case '=':
                    case '>':
                    case '<':
                    case '+':
                    case '-':
                    case '*':
                    case '%':
                    case '&':
                    case '|':
                    case '^':
                    case '"':
                    case '[':
                    case ']':
                    case '\\':
                        sb.Append("[").Append(c).Append("]");
                        break;
                    //case '\\':
                    //    sb.Append("\\").Append(c);
                    //    break;
                    case '\'':
                        sb.Append("''");
                        break;
                    default:
                        sb.Append(c);
                        break;

                }
                //if (c == '*' || c == '%' || c == '[' || c == ']')
                //    sb.Append("[").Append(c).Append("]");
                //else if (c == '\'')
                //    sb.Append("''");
                //else
                //    sb.Append(c);
            }
            return sb.ToString();
        }
    }
}
